﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using System.Configuration;
using System.Data.OleDb;
using System.Security.Cryptography;

namespace IISLogAnalyzer
{
    public static class SqlLiteHelper
    {
        private readonly static string _connectionString;

        static SqlLiteHelper()
        {
            // 读取App.config中的连接字符串  
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["SQLiteDbConnection"];
            if (settings == null)
            {
                throw new ConfigurationErrorsException("SQLite connection string is not found.");
            }
            _connectionString = settings.ConnectionString;
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(SQLString, connection))
                {
                    try
                    {
                        object obj = command.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.OleDb.OleDbException e)
                    {
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString)
        {
            DataSet ds = new DataSet();
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(SQLString, connection))
                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command))
                {
                    adapter.Fill(ds);
                }
            }
            return ds;
        }

        public static int ExecuteSql(string sql)
        {
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    return command.ExecuteNonQuery();
                }
            }
        }

        public static object ExecuteScalar(string sql)
        {
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    return command.ExecuteScalar();
                }
            }
        }

        public static DataTable ExecuteQuery(string sql)
        {
            DataTable dataTable = new DataTable();
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command))
                {
                    adapter.Fill(dataTable);
                }
            }
            return dataTable;
        }

        public static void ExecuteTransaction(Action<SQLiteConnection> transactionAction)
        {
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        transactionAction(connection);
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public static int GetMax(string tableName, string columnName)
        {
            string sql = "SELECT MAX(" + columnName + ") FROM " + tableName;
            return Convert.ToInt32(ExecuteScalar(sql));
        }

        public static int GetMin(string tableName, string columnName)
        {
            string sql = "SELECT MIN(" + columnName + ") FROM " + tableName;
            return Convert.ToInt32(ExecuteScalar(sql));
        }

        public static int GetCount(string tableName, string whereClause = null)
        {
            string sql = "SELECT COUNT(*) FROM " + tableName;
            if (!string.IsNullOrEmpty(whereClause))
            {
                sql += " WHERE " + whereClause;
            }
            return Convert.ToInt32(ExecuteScalar(sql));
        }

        public static int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    return command.ExecuteNonQuery();
                }
            }
        }

        public static object ExecuteScalar(string sql, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    return command.ExecuteScalar();
                }
            }
        }

        public static DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters)
        {
            DataTable dataTable = new DataTable();
            using (SQLiteConnection connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command))
                    {
                        adapter.Fill(dataTable);
                    }
                }
            }
            return dataTable;
        }



        // 示例方法，返回指定表的最大值  
        public static object GetMaxValue(string tableName, string columnName)
        {
            string sql = "SELECT MAX(" + columnName + ") FROM " + tableName;
            return ExecuteScalar(sql);
        }

        // 示例方法，返回指定表的最小值  
        public static object GetMinValue(string tableName, string columnName)
        {
            string sql = "SELECT MIN(" + columnName + ") FROM " + tableName;
            return ExecuteScalar(sql);
        }

        // 示例方法，返回指定条件下的记录数  
        public static int GetCount(string tableName, string whereClause, params SQLiteParameter[] parameters)
        {
            string sql = "SELECT COUNT(*) FROM " + tableName;
            if (!string.IsNullOrEmpty(whereClause))
            {
                sql += " WHERE " + whereClause;
            }
            return Convert.ToInt32(ExecuteScalar(sql, parameters));
        }
    }
}
